package org.exia.minisig.model.dao;

import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.exia.minisig.application.LogFile;
import org.exia.minisig.application.Utils;
import org.exia.minisig.model.database.Database;
import org.exia.minisig.model.object.DescriptibleElement;
import org.exia.minisig.model.object.News;

public class NewsDAO  extends Database implements DAO {
		//VAR
		private News news;
		//CONSTRUCTOR
		public NewsDAO() {}
		
		public NewsDAO(News _news) {
			initializeVar(_news);
		}
		public void initializeVar(News _news){
			this.news = _news;
		}
		
		public News getNews() {
			  return news;
			 }
		public void setNews(News news) {
			  this.news = news;
			 }
		//OVERRIDE
		@Override
		public void add() {
			try {
			  	this.connect();
		   		String jQuery = "INSERT INTO NEWS (LabelNews, DescriptifNews)" +
		       "VALUES  ('" + this.news.getLabel() + "', '" + this.news.getText() + "');";
		   		Statement addStatement;
			   addStatement = connection.createStatement();
			   addStatement.executeUpdate(jQuery);
			   addStatement.close();
		  	} catch (SQLException e) {
			  	LogFile.setEntry("Erreur - ajout itineraire a la bdd\n" + e.getMessage());
				System.out.println(e.getMessage());
		  	} finally {
			   this.close();
		    }
			
		}
		@Override
		public void delete() {
			// TODO Auto-generated method stub
			try {
			  	this.connect();
			  	String jQuery = "DELETE FROM NEWS WHERE IdNews =("+this.news.getId()+");";
		   		Statement addStatement;
			   addStatement = connection.createStatement();
			   addStatement.executeUpdate(jQuery);
			   addStatement.close();
		  	} catch (SQLException e) {
			  	LogFile.setEntry("Erreur - ajout itineraire a la bdd\n" + e.getCause());
				System.out.println(e.getMessage());
		  	} finally {
			   this.close();
		    }
		}
		@Override
		public void update() {		
			try {
				this.connect();
				String jQuery= "UPDATE NEWS  SET LabelNews='" + this.news.getLabel() + "',DescriptifNews='" + this.news.getText() +"' WHERE IdNews = "+ this.news.getId() +";";
				Statement addStatement;
				   addStatement = connection.createStatement();
				   addStatement.executeUpdate(jQuery);
				   addStatement.close();
			  	} catch (SQLException e) {
				  	LogFile.setEntry("Erreur - ajout itineraire a la bdd\n" + e.getMessage());
					System.out.println(e.getMessage());
			  	} finally {
				   this.close();
			    }
		}
		
		public void linkImages(){
			  //1 - Copy
			  for (String img: this.news.getImages()) {
			   File fileImg = new File(img);
			   String test = fileImg.getName();
			   String test2= Utils.applicationFolder() + "News_img\\"+test;
			   fileImg.renameTo(new File(Utils.applicationFolder() + "News_img\\" + fileImg.getName()));
			   try{
			   this.connect();
			   //QUERY
			   String jQuery = "INSERT INTO PICTURES (UrlPictures, IdNews )VALUES ('" + test2 + "',"+ this.news.getId() +")";
			   //EXECUTION
			   Statement addStatement;
			   addStatement = connection.createStatement();
			   addStatement.executeQuery(jQuery);
			   addStatement.close();
			   }catch(SQLException e){
			   }finally{
			    this.close();
			   }
			   
			  }  
			 }
		
		@Override
		public ArrayList<Object> getList() {
			this.connect();
			ArrayList<Object> resultList = new ArrayList<>();
				try {
					Statement st = this.connection.createStatement();
					ResultSet rs= st.executeQuery("SELECT * FROM NEWS ");		
					while (rs.next())
					{ 
						News currentNews = new News();
						currentNews.setText(rs.getString("DescriptifNews"));
						currentNews.setLabel(rs.getString("LabelNews"));
						currentNews.setId(Integer.parseInt(rs.getString("IdNews")));
						resultList.add(currentNews);
				    }						
					for (Object object : resultList) {
						ResultSet rss= st.executeQuery("SELECT UrlPictures FROM PICTURES WHERE IdNews = "+ ((DescriptibleElement) object).getId()+";");
						try{
							while (rss.next()){
								((DescriptibleElement) object).addImage(rss.getString("UrlPictures"));
							}
						}
						catch(SQLException e1){
							System.err.println("erreur ajout image lors de creation liste des news");
						}
						rss= st.executeQuery("SELECT UrlLink FROM URLS WHERE IdNews = "+ ((DescriptibleElement) object).getId()+";");
						try{
							while (rss.next()){
								((DescriptibleElement) object).addURL(rss.getString("UrlLink"));
							}
						}
						catch(SQLException e1){
							System.err.println("erreur ajout url lors de creation liste des news");
						}
					}		
					st.close();
					connection.close();
				} 
				catch (SQLException e2) {
					System.err.println("erreur getList News");
				}			
			return resultList;
		}
		@Override
		public void setID() {
			 try {
				  this.connect();
				  	//RECUPERATION ID
				   	String jQuery = " SELECT IdNews FROM NEWS ORDER BY IdNews DESC";
				   	Statement addStatement;
				   	addStatement = connection.createStatement();
			   		ResultSet result = addStatement.executeQuery(jQuery);
				   	while (result.next()) {
				   		this.news.setId(result.getInt("IdNews"));
				    	break;
				   	}
				   	addStatement.close();
				   	result.close();
			  	} catch (SQLException e) {
				  	LogFile.setEntry("Erreur - set id news \n" + e.getCause());
					System.out.println(e.getMessage());
			  	} finally {
			  		this.close();
			    }
		}
		
		public void linkURL() {
			for (String url: this.news.getUrls()) {
				   try{
					   this.connect();
					   //QUERY
					   String jQuery = "INSERT INTO URLS (UrlLink, IdNews)  VALUES('"+ url +"'," + this.news.getId() +");";
					   //EXECUTION
					   Statement addStatement;
					   addStatement = connection.createStatement();
					   addStatement.executeQuery(jQuery);
					   addStatement.close();
				   }catch(SQLException e){
				   }finally{
				    this.close();
				   }
			}
		}

		
		
		public void deleteImg(){
			try {
			  	this.connect();
		   		   		   		
		   		String jQuery = "DELETE FROM PICTURES WHERE IdNews =("+this.news.getId()+");";
		   		Statement addStatement;
			   addStatement = connection.createStatement();
			   addStatement.executeUpdate(jQuery);
			   addStatement.close();
		  	} catch (SQLException e) {
			  	LogFile.setEntry("Erreur - ajout itineraire a la bdd\n" + e.getMessage());
				System.out.println(e.getMessage());
		  	} finally {
			   this.close();
		    }
		}

		public void deleteUrl(){
			 try {
				  	this.connect();
			   		   		   		
			   		String jQuery = "DELETE FROM URLS WHERE IdNews =("+this.news.getId()+");";
			   		Statement addStatement;
				   addStatement = connection.createStatement();
				   addStatement.executeUpdate(jQuery);
				   addStatement.close();
			  	} catch (SQLException e) {
				  	LogFile.setEntry("Erreur - ajout itineraire a la bdd\n" + e.getMessage());
					System.out.println(e.getMessage());
			  	} finally {
				   this.close();
			    }
		}

}


